﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Data.SqlServerCe;

namespace ERPForms
{
   public class SqlTableCreator
    {
        #region Instance Variables
        private SqlCeConnection _connection;
        public SqlCeConnection SourceConnection
        {
            get { return _connection; }
            set { _connection = value; }
        }

        private SqlCeTransaction _transaction;
        public SqlCeTransaction Transaction
        {
            get { return _transaction; }
            set { _transaction = value; }
        }
 
        private string _tableName;
        public string DestinationTableName
        {
            get { return _tableName; }
            set { _tableName = value; }
        }
        #endregion
 
        #region Constructor
        public SqlTableCreator() { }
        public SqlTableCreator(SqlCeConnection connection) : this(connection, null) { }
        public SqlTableCreator(SqlCeConnection connection, SqlCeTransaction transaction)
        {
            _connection = connection;
            _transaction = transaction;
        }
        #endregion
 
        #region Instance Methods
        public object Create(DataTable schema,out String comanda)
        {
            return Create(schema, null, out comanda);
        }
        public object Create(DataTable schema, int numKeys, out String comanda)
        {
            int[] primaryKeys = new int[numKeys];
            for (int i = 0; i < numKeys; i++)
            {
                primaryKeys[i] = i;
            }
            return Create(schema, primaryKeys,out comanda);
        }
        public object Create(DataTable schema, int[] primaryKeys, out String comanda)
        {
            string sql = GetCreateSQL(_tableName, schema, primaryKeys);
 
            SqlCeCommand cmd;
            if (_transaction != null && _transaction.Connection != null)
                cmd = new SqlCeCommand(sql, _connection, _transaction);
            else
                cmd = new SqlCeCommand(sql, _connection);
            comanda = cmd.CommandText;
            return cmd.ExecuteNonQuery();
        }
 
        public object CreateFromDataTable(DataTable table,DataTable table_struct,out String comanda)
        {
            string sql = GetCreateFromDataTableSQL(_tableName, table_struct,table);
 
            SqlCeCommand cmd;
            if (_transaction != null && _transaction.Connection != null)
                cmd = new SqlCeCommand(sql, _connection, _transaction);
            else
                cmd = new SqlCeCommand(sql, _connection);
            comanda = cmd.CommandText;
 
            return cmd.ExecuteNonQuery();
        }

        public object CreateFromDataRelation(DataRelation table, out String comanda)
        {
            string sql = GetCreateFromDataRelationSQL(_tableName, table);

            SqlCeCommand cmd;
            if (_transaction != null && _transaction.Connection != null)
                cmd = new SqlCeCommand(sql, _connection, _transaction);
            else
                cmd = new SqlCeCommand(sql, _connection);
            comanda = cmd.CommandText;

            return cmd.ExecuteNonQuery();
        }

        public object CreateFromDataRow(DataTable table, DataRow dtr,out String comanda)
        {
            string sql = GetInsertFromDataTableSQL(table,dtr);

            SqlCeCommand cmd;
            if (_transaction != null && _transaction.Connection != null)
                cmd = new SqlCeCommand(sql, _connection, _transaction);
            else
                cmd = new SqlCeCommand(sql, _connection);
            comanda = cmd.CommandText;

            return cmd.ExecuteNonQuery();
        }

        public object CreateFromDataRow(DataTable table, DataRowView dtr, out String comanda)
        {
            return CreateFromDataRow(table,dtr.Row, out comanda);
        }

        #endregion
 
        #region Static Methods
 
        public static string GetCreateSQL(string tableName, DataTable schema, int[] primaryKeys)
        {
            string sql = "CREATE TABLE [" + tableName + "] (\n";
 
            // columns
            foreach (DataRow column in schema.Rows)
            {
                if (!(schema.Columns.Contains("IsHidden") && (bool)column["IsHidden"]))
                {
                    sql += "\t[" + column["ColumnName"].ToString() + "] " + SQLGetType(column);
 
                    if (schema.Columns.Contains("AllowDBNull") && (bool)column["AllowDBNull"] == false)
                        sql += " NOT NULL";
 
                    sql += ",\n";
                }
            }
            sql = sql.TrimEnd(new char[] { ',', '\n' }) + "\n";
 
            // primary keys
            string pk = ", CONSTRAINT PK_" + tableName + " PRIMARY KEY (";
            bool hasKeys = (primaryKeys != null && primaryKeys.Length > 0);
            if (hasKeys)
            {
                // user defined keys
                foreach (int key in primaryKeys)
                {
                    pk += schema.Rows[key]["ColumnName"].ToString() + ", ";
                }
            }
            else
            {
                // check schema for keys
                string keys = string.Join(", ", GetPrimaryKeys(schema));
                pk += keys;
                hasKeys = keys.Length > 0;
            }
            pk = pk.TrimEnd(new char[] { ',', ' ', '\n' }) + ")\n";
            if (hasKeys) sql += pk;
 
            sql += ")";
 
            return sql;
        }
 
        public static string GetCreateFromDataTableSQL(string tableName, DataTable dataStru,DataTable table)
        {
            if (tableName == null || tableName == String.Empty)
                tableName = table.TableName;
            string sql = "CREATE TABLE [" + tableName + "] (\n";
            // columns
            foreach (DataColumn column in table.Columns)
            {
                sql += "[" + column.ColumnName + "] " + SQLGetType(column,dataStru) + ",\n";
            }
            sql = sql.TrimEnd(new char[] { ',', '\n' }) + "\n";
            // primary keys
            if (table.PrimaryKey.Length > 0)
            {
                sql += ", CONSTRAINT [PK_" + tableName + "] PRIMARY KEY (";
                foreach (DataColumn column in table.PrimaryKey)
                {
                    sql += "[" + column.ColumnName + "],";
                    //sql += column.ColumnName + ",";
                }
                sql = sql.TrimEnd(new char[] { ',' }) + "))\n";
            }

            //if not ends with ")"
            if ((table.PrimaryKey.Length == 0) && (!sql.EndsWith(")")))
            {
                sql += ")";
            }
 
            return sql;
        }

        public static string GetCreateFromDataRelationSQL(string tableName, DataRelation relation)
        {
            if (tableName == null || tableName == String.Empty)
                tableName = relation.RelationName;
            string sql = "ALTER TABLE  [" + relation.ChildTable + "] ADD CONSTRAINT [" + relation.RelationName + "] FOREIGN KEY  (";
            // columns
            foreach (DataColumn column in relation.ChildColumns)
            {
                sql += "[" + column.ColumnName + "]" + ",";
            }
            sql = sql.TrimEnd(new char[] { ',', '\n' }) + ")";
            sql += " REFERENCES [" + relation.ParentTable + "] (";
            // parent keys
            foreach (DataColumn column in relation.ParentColumns)
            {
                sql += "[" + column.ColumnName + "],";
                //sql += column.ColumnName + ",";
            }
            sql = sql.TrimEnd(new char[] { ',', '\n' }) + ")";

            return sql;
        }

        public static string GetInsertFromDataTableSQL(DataTable table,DataRow dataRow)
        {
            string sql = "INSERT INTO  [" + table.TableName + "]  (";
            // columns
            foreach (DataColumn column in table.Columns)
            {
                sql += "[" + column.ColumnName + "]" + ",";
            }
            sql = sql.TrimEnd(new char[] { ',', '\n' }) + ")";
            sql += " VALUES (";
            // parent keys
            foreach (DataColumn column in table.Columns)
            {
                if (dataRow[column.ColumnName] == null || dataRow[column.ColumnName] == DBNull.Value)
                    sql += "NULL,";
                else
                {
                    if (column.DataType == typeof(String))
                    {
                        String val = dataRow[column.ColumnName].ToString().Replace("'", "''");
                        sql += "'" + val + "',";
                    }
                    else
                        if (column.DataType == typeof(Boolean))
                            sql += "" + (Convert.ToBoolean(dataRow[column.ColumnName]) ? "1" : "0") + ",";
                        else
                            if (column.DataType == typeof(DateTime))
                            {
                                DateTime tmpDt = Convert.ToDateTime(dataRow[column.ColumnName]);
                                sql += "'" + tmpDt.Year.ToString() + "-" + tmpDt.Month.ToString() + "-" + tmpDt.Day.ToString() + " " +
                                    tmpDt.Hour.ToString() + ":" + tmpDt.Minute.ToString() + ":" + tmpDt.Second.ToString() + "',";
                            }
                            else
                                if (column.DataType == typeof(Guid))
                                {
                                    sql += "'" + dataRow[column.ColumnName].ToString() + "',";
                                }
                                else
                                    if (column.DataType == typeof(Int64) || column.DataType == typeof(Int32))
                                    {
                                        if (column.ColumnName.ToLower() == "iddocument" || column.ColumnName.ToLower() == "iddocumentplata"
                                            || column.ColumnName.ToLower() == "iddocplatadetaliu"
                                            || column.ColumnName.ToLower() == "iddocumentdetaliu"
                                            || column.ColumnName.ToLower() == "codformula"
                                            || column.ColumnName.ToLower() == "idarticol")

                                            sql += "-" + dataRow[column.ColumnName].ToString() + ",";
                                        else
                                            sql += "" + dataRow[column.ColumnName].ToString() + ",";
                                    }
                                    else
                                        sql += "" + dataRow[column.ColumnName].ToString() + ",";

                }
            }
            sql = sql.TrimEnd(new char[] { ',', '\n' }) + ")";

            return sql;
        }


        public static string GetInsertFromDataTableSQL(DataTable table,DataRowView dataRow)
        {
            return GetInsertFromDataTableSQL(table,dataRow.Row);
        }
 
        public static string[] GetPrimaryKeys(DataTable schema)
        {
            List<string> keys = new List<string>();
 
            foreach (DataRow column in schema.Rows)
            {
                if (schema.Columns.Contains("IsKey") && (bool)column["IsKey"])
                    keys.Add(column["ColumnName"].ToString());
            }
 
            return keys.ToArray();
        }
 
        // Return T-SQL data type definition, based on schema definition for a column
        public static string SQLGetType(object type, int columnSize, int numericPrecision, int numericScale)
        {
            switch (type.ToString())
            {
                case "System.String":
                    return (columnSize > 4000) ? "ntext": "nchar(" + ((columnSize == -1) ? "255" : columnSize.ToString()) + ")";
 
                case "System.Decimal":
                    return "numeric(" + numericPrecision.ToString() + "," + numericScale.ToString() + ")";
                    //if (numericScale > 0)
                    //    return "real";
                    //else if (numericPrecision > 10)
                    //    return "bigint";
                    //else
                    //    return "INT";
 
                case "System.Double":
                case "System.Single":
                    return "real";
 
                case "System.Int64":
                    return "bigint";
 
                case "System.Int16":
                case "System.Int32":
                    return "integer";
 
                case "System.DateTime":
                    return "datetime";
 
                case "System.Boolean":
                    return "bit";
 
                case "System.Byte":
                    return "tinyint";
 
                case "System.Guid":
                    return "uniqueidentifier";
 
                default:
                    throw new Exception(type.ToString() + " not implemented.");
            }
        }
 
        // Overload based on row from schema table
        public static string SQLGetType(DataRow schemaRow)
        {
            return SQLGetType(schemaRow["DataType"],
                                int.Parse(schemaRow["ColumnSize"].ToString()),
                                int.Parse(schemaRow["NumericPrecision"].ToString()),
                                int.Parse(schemaRow["NumericScale"].ToString()));
        }
        // Overload based on DataColumn from DataTable type
        public static string SQLGetType(DataColumn column,DataTable datastru)
        {
            int numericPrecisin=0, numericScale = 0;
            DataRow[] dr = datastru.Select("TABLE_NAME = '" +column.Table.TableName + "' AND COLUMN_NAME='" + column.ColumnName + "'");
            if (dr.Length != 0)
            {
                if (dr[0]["DATA_TYPE"].ToString() == "decimal")
                {
                    numericPrecisin = Convert.ToInt32(dr[0]["NUMERIC_PRECISION"]);
                    numericScale = Convert.ToInt32(dr[0]["NUMERIC_SCALE"]);
                }
            }

            return SQLGetType(column.DataType, column.MaxLength, numericPrecisin, numericScale);
        }
        #endregion
    }

}
